﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CaseReview.DBUtility;
using CaseReview.Model;

namespace CaseReview.SQLServerDAL
{
    public class Case {

        private const string SQL_INSERT_CASEINFO = @"INSERT INTO dbo.CaseInfo (CaseId,Title,Age,Labor,Idle,Owner,Severity,Phase,
                                                    ServiceLevel,Type,CountOfOwnership,Status,date,Reviewer,ReviewerName) values(@CaseId,@Title,@Age,
                                                    @Labor,@Idle,@Owner,@Severity,@Phase,@ServiceLevel,@Type,@CountOfOwnership,@Status,
                                                    @date,@Reviewer,@ReviewerName)";
        private const string SQL_UPDATE_CASEINFO = @"UPDATE dbo.CaseInfo SET CaseId = @CaseId, Title = @Title, Age = @Age, Labor = @Labor,
                                                    Idle = @Idle, Owner=@Owner,Severity= @Severity, Phase = @Phase, ServiceLevel = @ServiceLevel,
                                                    Type =@Type, CountOfOwnership = @CountOfOwnership,Status =@Status,date = @date,Reviewer = @Reviewer ,ReviewerName = @ReviewerName
                                                     WHERE CaseID = @OldCaseId";
        private const string SQL_SELECT_TOTAL_CASE_BY_STATUS = @"SELECT CaseId,Title,Age,Labor,Idle,Owner,Severity,Phase,ServiceLevel,Type,
                                                        CountOfOwnership,Status,date,Reviewer,ReviewerName FROM dbo.CaseInfo WHERE Status = @Status";
        private const string SQL_SELECT_TOTAL_CASE_BY_REVIEWER = @"SELECT CaseId,Title,Age,Labor,Idle,Owner,Severity,Phase,ServiceLevel,Type,
                                                        CountOfOwnership,Status,date,Reviewer,ReviewerName FROM dbo.CaseInfo WHERE Reviewer = @Reviewer";

        private const string SQL_SELECT_CASE_BY_CASEID = @"SELECT CaseId,Title,Age,Labor,Idle,Owner,Severity,Phase,ServiceLevel,Type,
                                                        CountOfOwnership,Status,date,Reviewer,ReviewerName FROM dbo.CaseInfo WHERE CaseId = @CaseId";

        private const string PARM_CASE_ID = "@CaseId";
        private const string PARM_TITLE = "@Title";
        private const string PARM_AGE = "@Age";
        private const string PARM_LABOR = "@Labor";
        private const string PARM_IDLE = "@Idle";
        private const string PARM_OWNER = "@Owner";
        private const string PARM_SEVERITY = "@Severity";
        private const string PARM_PHASE = "@Phase";
        private const string PARM_SERVICELEVEL = "@ServiceLevel";
        private const string PARM_TYPE = "@Type";
        private const string PARM_COUNTOFOWNERSHIP = "@CountOfOwnership";
        private const string PARM_STATUS = "@Status";
        private const string PARM_DATE = "@date";
        private const string PARM_REVIEWER = "@Reviewer";
        private const string PARM_REVIEWERNAME = "@ReviewerName";
        private const string PARM_OLD_CASE_ID = "@OldCaseId";

        public void Insert(CaseInfo caseInfo) {
           StringBuilder stringBuilder = new StringBuilder();
           SqlParameter[] sqlParameters = GetCaseInfoParameters();
           SqlCommand sqlCommand = new SqlCommand();
            sqlParameters[0].Value = caseInfo.CaseId;
            sqlParameters[1].Value = caseInfo.Title;
            sqlParameters[2].Value = caseInfo.Age;
            sqlParameters[3].Value = caseInfo.Labor;
            sqlParameters[4].Value = caseInfo.Idle;
            sqlParameters[5].Value = caseInfo.Owner;
            sqlParameters[6].Value = caseInfo.Severity;
            sqlParameters[7].Value = caseInfo.Phase;
            sqlParameters[8].Value = caseInfo.ServicelLevel;
            sqlParameters[9].Value = caseInfo.Type;
            sqlParameters[10].Value = caseInfo.CountOfOwner;
            sqlParameters[11].Value = caseInfo.Status;
            sqlParameters[12].Value = caseInfo.Date;
            sqlParameters[13].Value = caseInfo.Reviewer;
            sqlParameters[14].Value = caseInfo.ReviewerName;

            SqlHelper.executeNonQuery(SQL_INSERT_CASEINFO, sqlParameters);
        }

        

        public CaseInfo GetCaseInfo(string caseId)
        {
            CaseInfo caseInfo = null;
            SqlParameter[] sqlParameters = { new SqlParameter(PARM_CASE_ID, SqlDbType.NVarChar) };
            sqlParameters[0].Value = caseId;
            using (SqlDataReader sdr = SqlHelper.executeReader(SQL_SELECT_CASE_BY_CASEID, sqlParameters)) {
                while (sdr.Read())
                {
                    caseInfo = new CaseInfo(sdr.GetString(0), sdr.GetString(1), sdr.GetString(2), sdr.GetString(3),
                            sdr.GetString(4), sdr.GetString(5), sdr.GetString(6), sdr.GetString(7), sdr.GetString(8), sdr.GetString(9),
                            sdr.GetString(10), sdr.GetInt32(11), sdr.GetDateTime(12), sdr.GetInt32(13), sdr.GetString(14));
                }
            }
            return caseInfo;
        }

        public IList<CaseInfo> GetTotalCaseByStatus(int status) {
            IList<CaseInfo> caseInfos = new List<CaseInfo>();
            SqlParameter[] sqlParameters = {new SqlParameter(PARM_STATUS, SqlDbType.Int)};
            sqlParameters[0].Value = status;
            using (SqlDataReader sdr = SqlHelper.executeReader(SQL_SELECT_TOTAL_CASE_BY_STATUS,sqlParameters))
            {
                while (sdr.Read()) {
                    caseInfos.Add(new CaseInfo(sdr.GetString(0),sdr.GetString(1),sdr.GetString(2),sdr.GetString(3),
                        sdr.GetString(4),sdr.GetString(5),sdr.GetString(6),sdr.GetString(7),sdr.GetString(8),sdr.GetString(9),
                        sdr.GetString(10), sdr.GetInt32(11), sdr.GetDateTime(12), sdr.GetInt32(13), sdr.GetString(14)));
                }    
            }
            return caseInfos;
        }

        public IList<CaseInfo> GetTotalCaseByReviewer(int reviewer)
        {
            IList<CaseInfo> caseInfos = new List<CaseInfo>();
            SqlParameter[] sqlParameters = { new SqlParameter(PARM_REVIEWER, SqlDbType.Int) };
            sqlParameters[0].Value = reviewer;
            using (SqlDataReader sdr = SqlHelper.executeReader(SQL_SELECT_TOTAL_CASE_BY_REVIEWER, sqlParameters))
            {
                while (sdr.Read())
                {
                    caseInfos.Add(new CaseInfo(sdr.GetString(0), sdr.GetString(1), sdr.GetString(2), sdr.GetString(3),
                        sdr.GetString(4), sdr.GetString(5), sdr.GetString(6), sdr.GetString(7), sdr.GetString(8), sdr.GetString(9),
                        sdr.GetString(10), sdr.GetInt32(11), sdr.GetDateTime(12), sdr.GetInt32(13), sdr.GetString(14)));
                }
            }
            return caseInfos;
        }

        public void UpdateCase(CaseInfo caseInfo,string oldCaseId) {
            SqlParameter[] sqlParameters = { new SqlParameter(PARM_CASE_ID,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_TITLE,SqlDbType.Text),
                                                                  new SqlParameter(PARM_AGE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_LABOR,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_IDLE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_OWNER,SqlDbType.VarChar,20),
                                                                  new SqlParameter(PARM_SEVERITY,SqlDbType.VarChar,1),
                                                                  new SqlParameter(PARM_PHASE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_SERVICELEVEL,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_TYPE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_COUNTOFOWNERSHIP,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_STATUS,SqlDbType.Int),
                                                                  new SqlParameter(PARM_DATE,SqlDbType.DateTime,12), 
                                                                  new SqlParameter(PARM_REVIEWER,SqlDbType.Int),
                                                                  new SqlParameter(PARM_REVIEWERNAME,SqlDbType.VarChar,20),
                                                                  new SqlParameter(PARM_OLD_CASE_ID,SqlDbType.VarChar,50)};
            sqlParameters[0].Value = caseInfo.CaseId;
            sqlParameters[1].Value = caseInfo.Title;
            sqlParameters[2].Value = caseInfo.Age;
            sqlParameters[3].Value = caseInfo.Labor;
            sqlParameters[4].Value = caseInfo.Idle;
            sqlParameters[5].Value = caseInfo.Owner;
            sqlParameters[6].Value = caseInfo.Severity;
            sqlParameters[7].Value = caseInfo.Phase;
            sqlParameters[8].Value = caseInfo.ServicelLevel;
            sqlParameters[9].Value = caseInfo.Type;
            sqlParameters[10].Value = caseInfo.CountOfOwner;
            sqlParameters[11].Value = caseInfo.Status;
            sqlParameters[12].Value = caseInfo.Date;
            sqlParameters[13].Value = caseInfo.Reviewer;
            sqlParameters[14].Value = caseInfo.ReviewerName;
            sqlParameters[15].Value = oldCaseId;
            SqlHelper.executeNonQuery(SQL_UPDATE_CASEINFO, sqlParameters);
        }


        private static SqlParameter[] GetCaseInfoParameters() {
            SqlParameter[] sqlParameters = new SqlParameter[] {
                                                                  new SqlParameter(PARM_CASE_ID,SqlDbType.BigInt),
                                                                  new SqlParameter(PARM_TITLE,SqlDbType.Text),
                                                                  new SqlParameter(PARM_AGE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_LABOR,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_IDLE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_OWNER,SqlDbType.VarChar,20),
                                                                  new SqlParameter(PARM_SEVERITY,SqlDbType.VarChar,1),
                                                                  new SqlParameter(PARM_PHASE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_SERVICELEVEL,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_TYPE,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_COUNTOFOWNERSHIP,SqlDbType.VarChar,50),
                                                                  new SqlParameter(PARM_STATUS,SqlDbType.Int),
                                                                  new SqlParameter(PARM_DATE,SqlDbType.DateTime,12), 
                                                                  new SqlParameter(PARM_REVIEWER,SqlDbType.Int),
                                                                  new SqlParameter(PARM_REVIEWERNAME,SqlDbType.VarChar,20),
                                                              };
            return sqlParameters;
        }
    }
}
